﻿using FinancialReportPowerPointAddIn.Helper;
using Microsoft.EntityFrameworkCore.Metadata.Internal;
using Microsoft.Office.Interop.PowerPoint;
using System;
using System.Collections.Generic;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace FinancialReportPowerPointAddIn.PPTModule
{
    public static class GenerateYtdPLData
    {
        public static void Write(int year, int month)
        {
            //获取当前ppt中所有的幻灯片
            var slides = Globals.ThisAddIn.Application.ActivePresentation.Slides;
            //在当前选中幻灯片前插入新的幻灯片--代码提示中没有F12进去才找到的方法
            Slide currentslide = slides.Add(slides.Count + 1, PpSlideLayout.ppLayoutBlank);
            currentslide.Select();
            //写入标题           
            Shape titleBox = currentslide.Shapes.AddTextbox(Microsoft.Office.Core.MsoTextOrientation.msoTextOrientationHorizontal, 10, 30, 370, 15);
            titleBox.TextFrame.TextRange.Text = $"诚福公司P&L数据{year}{month.ToString("00")} YTD";
            titleBox.TextFrame.TextRange.Font.Bold = Microsoft.Office.Core.MsoTriState.msoCTrue;
            //写入表格            
            Shape tableShape = currentslide.Shapes.AddTable(30, 11, 75, 75, currentslide.Master.Width, currentslide.Master.Height - 75);
            Table table = tableShape.Table;
            // 编写标题行
            table.Cell(1, 1).Shape.TextFrame.TextRange.Text = "YTD";
            table.Cell(1, 1).Shape.TextFrame.TextRange.Font.Color.RGB = Color.FromArgb(0, 0, 0).ToArgb();
            table.Cell(1, 2).Shape.TextFrame.TextRange.Text = $"{year}年计划";
            table.Cell(1, 2).Shape.TextFrame.TextRange.Font.Color.RGB = Color.FromArgb(0, 0, 0).ToArgb();
            table.Cell(1, 2).Merge(tableShape.Table.Cell(1, 3));
            table.Cell(1, 4).Shape.TextFrame.TextRange.Text = $"{year - 1}年实际";
            table.Cell(1, 4).Shape.TextFrame.TextRange.Font.Color.RGB = Color.FromArgb(0, 0, 0).ToArgb();
            table.Cell(1, 4).Merge(tableShape.Table.Cell(1, 5));
            table.Cell(1, 6).Shape.TextFrame.TextRange.Text = $"{year}年实际";
            table.Cell(1, 6).Shape.TextFrame.TextRange.Font.Color.RGB = Color.FromArgb(0, 0, 0).ToArgb();
            table.Cell(1, 6).Merge(tableShape.Table.Cell(1, 7));
            table.Cell(1, 8).Shape.TextFrame.TextRange.Text = "VS Plan";
            table.Cell(1, 8).Shape.TextFrame.TextRange.Font.Color.RGB = Color.FromArgb(0, 0, 0).ToArgb();
            table.Cell(1, 8).Merge(tableShape.Table.Cell(1, 9));
            table.Cell(1, 10).Shape.TextFrame.TextRange.Text = "VS 去年";
            table.Cell(1, 10).Shape.TextFrame.TextRange.Font.Color.RGB = Color.FromArgb(0, 0, 0).ToArgb();
            table.Cell(1, 10).Merge(tableShape.Table.Cell(1, 11));
            string[] arr = new string[] { "产品净收入", "食品成本", "包装成本", "运费", "毛利", "员工薪资", "管理组薪资", "薪资税", "广告", "促销", "外部服务", "营运物料", "维护保养", "公共事业", "可控利润", "租金", "服务费", "法律费用", "折旧", "其他费用", "不可控费用", "非产品净收入", "非产品成本", "非产品利润", "营运利润", "餐厅现金流", "特许经营行政费用", "再投资", "债前现金流" };
            //写入MTD  要调转颜色RGB =>BGR            
            int writeRow = 2;
            using (var db = new FinancialReportContext())
            {
                List<PLData> pladata = db.PLDatas.Where(x => x.IsPlan == false && x.Restaurant == "总表" && x.Year == year && x.Month <= month).ToList();
                List<PLData> plpdata = db.PLDatas.Where(x => x.IsPlan == true && x.Restaurant == "总表" && x.Year == year && x.Month <= month).ToList();
                List<PLData> plldata = db.PLDatas.Where(x => x.IsPlan == false && x.Restaurant == "总表" && x.Year == year - 1 && x.Month <= month).ToList();
                decimal planSales = plpdata.Sum(x => x.产品净收入);
                decimal actualSales = pladata.Sum(x => x.产品净收入);
                decimal lySales = plldata.Sum(x => x.产品净收入);
                // 获取 Sum 方法的信息
                MethodInfo sumMethod = typeof(Enumerable).GetMethods().Where(m => m.Name == "Sum" && m.ReturnType == typeof(decimal) && m.GetParameters().Length == 2).First().MakeGenericMethod(typeof(PLData));
                foreach (string item in arr)
                {
                    // 通过反射调用 Sum 方法，使用变量代替属性名                    
                    decimal plan = Convert.ToDecimal(sumMethod.Invoke(null, new object[] { plpdata, CreateSelector<PLData>(item) }));
                    decimal actual = Convert.ToDecimal(sumMethod.Invoke(null, new object[] { pladata, CreateSelector<PLData>(item) }));
                    decimal ly = Convert.ToDecimal(sumMethod.Invoke(null, new object[] { plldata, CreateSelector<PLData>(item) }));
                    //1列
                    table.Cell(writeRow, 1).Shape.TextFrame.TextRange.Text = item;
                    //2列
                    table.Cell(writeRow, 2).Shape.TextFrame.TextRange.Text = plan.ToString("#,##0");
                    //3列
                    table.Cell(writeRow, 3).Shape.TextFrame.TextRange.Text = (plan / planSales).ToString("p");
                    //4列
                    table.Cell(writeRow, 4).Shape.TextFrame.TextRange.Text = ly.ToString("#,##0");
                    //5列
                    table.Cell(writeRow, 5).Shape.TextFrame.TextRange.Text = (ly / lySales).ToString("p");
                    //6列
                    table.Cell(writeRow, 6).Shape.TextFrame.TextRange.Text = actual.ToString("#,##0");
                    //7列
                    table.Cell(writeRow, 7).Shape.TextFrame.TextRange.Text = (actual / actualSales).ToString("p");
                    //8列
                    table.Cell(writeRow, 8).Shape.TextFrame.TextRange.Text = (actual - plan).ToString("#,##0");
                    //10列
                    table.Cell(writeRow, 10).Shape.TextFrame.TextRange.Text = (actual - ly).ToString("#,##0");
                    if (item != "产品净收入")
                    {
                        //9列
                        table.Cell(writeRow, 9).Shape.TextFrame.TextRange.Text = ((actual / actualSales) - (plan / planSales)).ToString("p");
                        //11列
                        table.Cell(writeRow, 11).Shape.TextFrame.TextRange.Text = ((actual / actualSales) - (ly / lySales)).ToString("p");
                    }
                    switch (item)
                    {
                        case "毛利":
                        case "员工薪资":
                        case "管理组薪资":
                        case "薪资税":
                        case "维护保养":
                        case "公共事业":
                        case "可控利润":
                        case "不可控费用":
                        case "非产品利润":
                        case "营运利润":
                        case "餐厅现金流":
                            foreach (Cell cell in table.Rows[writeRow].Cells)
                            {
                                cell.Shape.TextFrame.TextRange.Font.Bold = Microsoft.Office.Core.MsoTriState.msoTrue;
                            }
                            break;
                        default:
                            break;
                    }
                    writeRow++;
                }
            }
            Common.AutoFit(table);
            Common.OrganizeFormat(table);
            Common.OrganizeFormat2(table);
            // 设置整个表格的单元格边距为无            
            tableShape.Height = currentslide.Master.Height - 85;
            tableShape.Width = currentslide.Master.Width - 90;
        }

        // 创建属性选择器
        static Func<T, decimal> CreateSelector<T>(string propertyName)
        {
            PropertyInfo property = typeof(T).GetProperty(propertyName);
            return (Func<T, decimal>)Delegate.CreateDelegate(typeof(Func<T, decimal>), property.GetGetMethod());
        }
    }
}
